package com.stylefeng.guns.modular.util;

import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.beans.BeanUtils;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;



/**
 * 
 * ClassName: ExcelBigUtil 
 * @Description: TODO大数据量excel处理工具类
 * @author xiongyao
 * @date 2017年9月20日
 */
public class ExcelBigUtil {

	
	
	
	
	public ExcelBigUtil() {
		super();
		// TODO Auto-generated constructor stub
	}



	//每行的数据
	private Map<String, Object> rowDataMap=new LinkedHashMap<String, Object>();
	
	//全部数据
	private List<Collection<Object>> dataList=new ArrayList<Collection<Object>>();
	
	//存储列明和列位置的对应关系
	private Map<String, String> columnMap=new LinkedHashMap<String, String>();
	
	//开始行数
	private long rowNum=1;
	
	//返回的对象列表
	private List<Object> resultList;
	
	//类
	private Class<?> clazz;
	
	
	//存放表头，key是excel的列，value是类的属性名
	public Map<String,String> headMap=new LinkedHashMap<String, String>();
	
	//存放表头名和类属性的对应关系
	public Map<String, String> popMap;
	
	
	public Class<?> getClazz() {
		return clazz;
	}

	public void setClazz(Class<?> clazz) {
		this.clazz = clazz;
	}

	public List<Object> getResultList() {
		return resultList;
	}

	public void setResultList(List<Object> resultList) {
		this.resultList = resultList;
	}

	public void processOneSheet(String filename,String sheetName) throws Exception {
		
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();

		XMLReader parser = fetchSheetParser(sst);

		// To look up the Sheet Name / Sheet Order / rID,
		//  you need to process the core Workbook stream.
		// Normally it's of the form rId# or rSheet#
		InputStream sheet2 = r.getSheet(sheetName);
		InputSource sheetSource = new InputSource(sheet2);
		parser.parse(sheetSource);
		sheet2.close();
	}

	public void processAllSheets(String filename) throws Exception {
		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader r = new XSSFReader( pkg );
		SharedStringsTable sst = r.getSharedStringsTable();
		
		XMLReader parser = fetchSheetParser(sst);

		Iterator<InputStream> sheets = r.getSheetsData();
		while(sheets.hasNext()) {
			System.out.println("Processing new sheet:\n");
			InputStream sheet = sheets.next();
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
			sheet.close();
			System.out.println("");
		}
	}

	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
		XMLReader parser =
			XMLReaderFactory.createXMLReader(
					"org.apache.xerces.parsers.SAXParser"
			);
		ContentHandler handler = new SheetHandler(sst);
		parser.setContentHandler(handler);
		return parser;
	}

	/** 
	 * See org.xml.sax.helpers.DefaultHandler javadocs 
	 */
	private class SheetHandler extends DefaultHandler {
		private SharedStringsTable sst;
		private String lastContents;
		private boolean nextIsString;
		private String index;
		private SheetHandler(SharedStringsTable sst) {
			this.sst = sst;
		}
		

		public void startElement(String uri, String localName, String name,
				Attributes attributes) throws SAXException {
			// c => cell
			if(name.equals("c")) {
				// Print the cell reference
				//System.out.println(attributes.getValue("r") + " - "+attributes.getValue("t"));
				//当前读取的位置
				index=attributes.getValue("r");
				//在第一行的时候，初始化列的key，默认第一行是完整的列，没有空值
				if(rowNum==1){
					columnMap.put(index.replaceAll("[^(A-Za-z)]", ""), "");
				}
				
				//如果是新的一行，把rowData放到allData中，然后清空当前rowData
				if(Pattern.compile("^A[0-9]+$").matcher(index).find()){ 
					 //存储上一行数据  
                    if(rowDataMap!=null&&!rowDataMap.isEmpty()){  
                    	
                    	//在读完第一行后，把表头和实体类的属性对应关系对应上
                    	if(rowNum==1){
                    		//headMap, key是excel的列，如A,B,C.value是类的属性名，如name,sex
                    		//popMap,key是表头名，value是属性名
                    		//rowDataMap的第一行，key是列，value是表头名
                    		for (Entry<String, Object> entry : rowDataMap.entrySet()) {
								headMap.put(entry.getKey(), popMap.get(entry.getValue()));
							}
                    		System.out.println(headMap.toString());
                    	}
//                    	System.out.println("读取第:"+rowNum+"行:"+rowDataMap.values());
                    	Object obj=ReflectionUtil.newInstance(clazz);
                    	if (rowNum>1) {
                    		for (Entry<String, String> entry : headMap.entrySet()) {
                    			Object valueObject;
                    			try {
                    				Class<?> attrType=BeanUtils.findPropertyType(entry.getValue(),clazz);
                    				Method method = obj.getClass().getMethod("set" + StringUtil.toUpperCaseFirstOne(entry.getValue()), attrType);
                    				if (attrType == String.class) {
                                        valueObject = rowDataMap.get(entry.getKey());
                                    } else if (attrType == BigDecimal.class) {
                                        valueObject = new BigDecimal(rowDataMap.get(entry.getKey()).toString());
                                    } else if (attrType == long.class) {
                                        valueObject = (long) rowDataMap.get(entry.getKey());
                                    } else if (attrType == Double.class) {
                                        valueObject = Double.parseDouble(rowDataMap.get(entry.getKey()).toString()) ;
                                    } else if (attrType == Float.class) {
                                        valueObject = (float) rowDataMap.get(entry.getKey());
                                    } else if (attrType == int.class || attrType == Integer.class) {
                                        valueObject = (int)rowDataMap.get(entry.getKey());
                                    } else if (attrType == Short.class) {
                                        valueObject = Short.parseShort(rowDataMap.get(entry.getKey()).toString()) ;
                                    } else {
                                        valueObject =rowDataMap.get(entry.getKey());
                                    }
                    				method.invoke(obj, valueObject);
                    			} catch (SecurityException e) {
                    				// TODO Auto-generated catch block
                    				e.printStackTrace();
                    			} catch (NoSuchMethodException e1) {
                    				// TODO Auto-generated catch block
                    				e1.printStackTrace();
                    			} catch (IllegalAccessException e) {
                    				// TODO Auto-generated catch block
                    				e.printStackTrace();
                    			} catch (IllegalArgumentException e) {
                    				// TODO Auto-generated catch block
                    				e.printStackTrace();
                    				System.out.println("rowNum:"+rowNum+"===========key:"+entry.getKey()+"value:"+rowDataMap.get(entry.getKey()).toString());
                    			} catch (InvocationTargetException e) {
                    				// TODO Auto-generated catch block
                    				e.printStackTrace();
                    			}
                    		}
                    		resultList.add(obj);
                    		dataList.add(rowDataMap.values());
						}
                        rowNum++;
                    }
                    rowDataMap.putAll(columnMap);
				}
				// Figure out if the value is an index in the SST
				String cellType = attributes.getValue("t");
				if(cellType != null && cellType.equals("s")) {
					nextIsString = true;
				} else {
					nextIsString = false;
				}
			}
			// Clear contents cache
			lastContents = "";
		}
		
		public void endElement(String uri, String localName, String name)
				throws SAXException {
			// Process the last contents as required.
			// Do now, as characters() may be called more than once
			if(nextIsString) {
				int idx = Integer.parseInt(lastContents);
				lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
				nextIsString = false;
			}

			// v => contents of a cell
			// Output after we've seen the string contents
			if(name.equals("v")) {
//				System.out.println(lastContents);
				rowDataMap.put(index.replaceAll("[^(A-Za-z)]", ""), lastContents);
			}
		}

		@Override
		public void endDocument() throws SAXException {
			// TODO Auto-generated method stub
			super.endDocument();
			if(rowDataMap!=null&&!rowDataMap.isEmpty()){  
//            	System.out.println("读取第:"+rowNum+"行:"+rowDataMap.values());
            	dataList.add(rowDataMap.values());
            	System.out.println("--结束--共"+dataList.size()+"行数据");
                rowDataMap.clear();
                rowNum++;
            } 
		}
		
		
		public void characters(char[] ch, int start, int length)
				throws SAXException {
			lastContents += new String(ch, start, length);
		}
	}
	
	
	 
	
	
	//
//	public static <T> List<T> readOneSheetXls(String filePath,String sheetId,Class<?> clazz){
//		return null;
//	}
//	
	public static <T> List<T> readAllSheetXls(String filePath,Map map,Class<?> clazz) throws Exception{
		long startTime=System.currentTimeMillis();
		ExcelBigUtil example = new ExcelBigUtil();
		//存放excel表头和类属性的对应关系map
		List<Object> list=new ArrayList<Object>();
		example.setResultList(list);
		example.setClazz(clazz);
		example.popMap=map;
		example.processAllSheets(filePath);
		System.out.println("共耗时"+(System.currentTimeMillis()-startTime)/1000+"s");
		return (List<T>) list;
	}
	
	
	
//	public static void main(String[] args) throws Exception {
//		long startTime=System.currentTimeMillis();
//		ExcelBigUtil example = new ExcelBigUtil();
//		//example.processOneSheet("D:/Users/Administrator/Desktop/工作/17-19大数据框架/政企中心/7月关键人相关报表/7月关键人相关报表/政企直销队伍通信和信息化收入(客户经理收入明细表)201707.xlsx");
//		List<Object> list=new ArrayList<Object>();
//		example.setResultList(list);
//		example.setClazz(CmIncomeStatementMonth.class);
//		//example.processAllSheets(filePath);
//		example.popMap=ExcelUtil.getMap("二级单位:secondaryUnits,三级单位:threeUnit,客户经理姓名:accountmanagerName,员工编号:emId,ESOP工号:esopId,集团家数（家）合计:totalEnterprise,集团家数（家）A类:typeaEnterprise,集团家数（家）B类:typebEnterprise,集团家数（家）C类:typecEnterprise,集团家数（家）D类:typedEnterprise,当月累计通信和信息化收入:totalCiRevenuMonth,当月累计专线收入:totalAlRevenueMonth,当月累计集团成员统付收入:totalGmPayMonth,当月客户经理管辖12个月及以上集团的累计通信和信息化收入:totalCiRevenuTwelveMonth,当月客户经理管辖12个月及以上集团的专线收入:totalAlRevenueTwelveMonth,去年同期累计通信和信息化收入:lyCiRevenuMonth,当月客户经理管辖12个月及以上集团的去年同期累计通信和信息化收入:lyCiRevenuTwlMonth,去年同期累计专线收入:lyAlRevenueMonth,当月客户经理管辖12个月及以上集团的去年同期累计专线收入:lyCiRevenuTwelveMonth,月份:month");
//		example.processAllSheets("D:/Users/Administrator/Desktop/工作/17-19大数据框架/政企中心/7月关键人相关报表/7月关键人相关报表/政企直销队伍通信和信息化收入统计月报表201707.xlsx");
//		System.out.println("共耗时"+(System.currentTimeMillis()-startTime)/1000+"s");
//	}
}
